{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Module Feedback\n",
    "\n",
    "This system records the responses of students on their learning experience at university.\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/7/71/Module-feedback.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.1     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Find the student name from a matriculation number\n",
    "\n",
    "**Find the name of the student with number 50200100**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "ins_mod <- dbReadTable(con, 'INS_MOD')\n",
    "ins_prs <- dbReadTable(con, 'INS_PRS')\n",
    "cam_smo <- dbReadTable(con, 'CAM_SMO')\n",
    "ins_res <- dbReadTable(con, 'INS_RES')\n",
    "ins_que <- dbReadTable(con, 'INS_QUE')\n",
    "ins_spr <- dbReadTable(con, 'INS_SPR')\n",
    "ins_cat <- dbReadTable(con, 'INS_CAT')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>SPR_FNM1</th><th scope=col>SPR_SURN</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Tom</td><td>Cotton</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 2\n",
       "\\begin{tabular}{ll}\n",
       " SPR\\_FNM1 & SPR\\_SURN\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t Tom & Cotton\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 2\n",
       "\n",
       "| SPR_FNM1 &lt;chr&gt; | SPR_SURN &lt;chr&gt; |\n",
       "|---|---|\n",
       "| Tom | Cotton |\n",
       "\n"
      ],
      "text/plain": [
       "  SPR_FNM1 SPR_SURN\n",
       "1 Tom      Cotton  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ins_spr %>% \n",
    "    filter(SPR_CODE=='50200100') %>%\n",
    "    select(SPR_FNM1, SPR_SURN)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Find the modules studied by a student\n",
    "\n",
    "**Show the module code and module name for modules studied by the student with number 50200100 in session 2016/7 TR1**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 3 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>MOD_CODE</th><th scope=col>MOD_NAME</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>CSN08101</td><td>Systems and Services  </td></tr>\n",
       "\t<tr><td>INF08104</td><td>Database Systems      </td></tr>\n",
       "\t<tr><td>SET08108</td><td>Software Development 2</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 3 × 2\n",
       "\\begin{tabular}{ll}\n",
       " MOD\\_CODE & MOD\\_NAME\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t CSN08101 & Systems and Services  \\\\\n",
       "\t INF08104 & Database Systems      \\\\\n",
       "\t SET08108 & Software Development 2\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 3 × 2\n",
       "\n",
       "| MOD_CODE &lt;chr&gt; | MOD_NAME &lt;chr&gt; |\n",
       "|---|---|\n",
       "| CSN08101 | Systems and Services   |\n",
       "| INF08104 | Database Systems       |\n",
       "| SET08108 | Software Development 2 |\n",
       "\n"
      ],
      "text/plain": [
       "  MOD_CODE MOD_NAME              \n",
       "1 CSN08101 Systems and Services  \n",
       "2 INF08104 Database Systems      \n",
       "3 SET08108 Software Development 2"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# AYR_CODE length 7, so read as '2016/7 '\n",
    "# should trim it before processing\n",
    "ins_mod %>% \n",
    "    inner_join(\n",
    "        cam_smo %>%\n",
    "            filter(SPR_CODE=='50200100' &\n",
    "                   str_starts(AYR_CODE, '2016/7') &\n",
    "                   PSL_CODE=='TR1'),\n",
    "        by=c(MOD_CODE=\"MOD_CODE\")\n",
    "    ) %>%\n",
    "    select(MOD_CODE, MOD_NAME)    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Find the modules and module leader studied by a student\n",
    "\n",
    "**Show the module code and module name and details of the module leader for modules studied by the student with number 50200100 in session 2016/7 TR1**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 3 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>MOD_CODE</th><th scope=col>MOD_NAME</th><th scope=col>PRS_CODE</th><th scope=col>PRS_FNM1</th><th scope=col>PRS_SURN</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>CSN08101</td><td>Systems and Services  </td><td>40000008</td><td>James </td><td>Jackson </td></tr>\n",
       "\t<tr><td>INF08104</td><td>Database Systems      </td><td>40000036</td><td>Andrew</td><td>Cumming </td></tr>\n",
       "\t<tr><td>SET08108</td><td>Software Development 2</td><td>40000408</td><td>Neil  </td><td>Urquhart</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 3 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " MOD\\_CODE & MOD\\_NAME & PRS\\_CODE & PRS\\_FNM1 & PRS\\_SURN\\\\\n",
       " <chr> & <chr> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t CSN08101 & Systems and Services   & 40000008 & James  & Jackson \\\\\n",
       "\t INF08104 & Database Systems       & 40000036 & Andrew & Cumming \\\\\n",
       "\t SET08108 & Software Development 2 & 40000408 & Neil   & Urquhart\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 3 × 5\n",
       "\n",
       "| MOD_CODE &lt;chr&gt; | MOD_NAME &lt;chr&gt; | PRS_CODE &lt;chr&gt; | PRS_FNM1 &lt;chr&gt; | PRS_SURN &lt;chr&gt; |\n",
       "|---|---|---|---|---|\n",
       "| CSN08101 | Systems and Services   | 40000008 | James  | Jackson  |\n",
       "| INF08104 | Database Systems       | 40000036 | Andrew | Cumming  |\n",
       "| SET08108 | Software Development 2 | 40000408 | Neil   | Urquhart |\n",
       "\n"
      ],
      "text/plain": [
       "  MOD_CODE MOD_NAME               PRS_CODE PRS_FNM1 PRS_SURN\n",
       "1 CSN08101 Systems and Services   40000008 James    Jackson \n",
       "2 INF08104 Database Systems       40000036 Andrew   Cumming \n",
       "3 SET08108 Software Development 2 40000408 Neil     Urquhart"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cam_smo %>% \n",
    "    filter(SPR_CODE=='50200100' &\n",
    "           str_starts(AYR_CODE, '2016/7') &\n",
    "           PSL_CODE=='TR1') %>%\n",
    "    inner_join(ins_mod, by=c(MOD_CODE=\"MOD_CODE\")) %>%\n",
    "    inner_join(ins_prs, by=c(PRS_CODE=\"PRS_CODE\")) %>%\n",
    "    select(MOD_CODE, MOD_NAME, PRS_CODE, PRS_FNM1, PRS_SURN)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Show the scores for module SET08108\n",
    "\n",
    "**Show the Percentage of students who gave 4 or 5 to module SET08108 in session 2016/7 TR1**\n",
    "\n",
    "(note that this is not real data, these responses were randomly generated)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A grouped_df: 19 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>QUE_CODE</th><th scope=col>QUE_TEXT</th><th scope=col>CAT_NAME</th><th scope=col>pct</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>1.1</td><td>Staff are good at explaining things.                   </td><td>Learning and Teaching  </td><td>89</td></tr>\n",
       "\t<tr><td>1.2</td><td>Staff made the subject interesting.                    </td><td>Learning and Teaching  </td><td>82</td></tr>\n",
       "\t<tr><td>1.3</td><td>The module was intellectually stimulating.             </td><td>Learning and Teaching  </td><td>82</td></tr>\n",
       "\t<tr><td>1.4</td><td>The aims and objectives were clearly stated.           </td><td>Learning and Teaching  </td><td>89</td></tr>\n",
       "\t<tr><td>1.5</td><td>The module was well-organised and ran smoothly.        </td><td>Learning and Teaching  </td><td>78</td></tr>\n",
       "\t<tr><td>1.6</td><td>The pace was appropriate.                              </td><td>Learning and Teaching  </td><td>80</td></tr>\n",
       "\t<tr><td>1.7</td><td>The level was appropriate.                             </td><td>Learning and Teaching  </td><td>82</td></tr>\n",
       "\t<tr><td>1.8</td><td>The workload was managable.                            </td><td>Learning and Teaching  </td><td>78</td></tr>\n",
       "\t<tr><td>1.9</td><td>I was able to contact module staff when I needed to.   </td><td>Learning and Teaching  </td><td>76</td></tr>\n",
       "\t<tr><td>2.1</td><td>The assessment requirements were clearly stated.       </td><td>Assessment and Feedback</td><td>84</td></tr>\n",
       "\t<tr><td>2.2</td><td>The criteria for marking was made clear to me.         </td><td>Assessment and Feedback</td><td>82</td></tr>\n",
       "\t<tr><td>2.3</td><td>I was well supported for the assessment.               </td><td>Assessment and Feedback</td><td>80</td></tr>\n",
       "\t<tr><td>2.4</td><td>I was provided with feedback that aided understanding. </td><td>Assessment and Feedback</td><td>80</td></tr>\n",
       "\t<tr><td>2.5</td><td>Feedback was provided within three weeks of submission.</td><td>Assessment and Feedback</td><td>80</td></tr>\n",
       "\t<tr><td>3.1</td><td>The module was well supported by moodle.               </td><td>Learning Resources     </td><td>73</td></tr>\n",
       "\t<tr><td>3.2</td><td>The library resources met my needs.                    </td><td>Learning Resources     </td><td>82</td></tr>\n",
       "\t<tr><td>3.3</td><td>The IT resources met my needs.                         </td><td>Learning Resources     </td><td>89</td></tr>\n",
       "\t<tr><td>3.4</td><td>The rooms/facilities were of good quality.             </td><td>Learning Resources     </td><td>78</td></tr>\n",
       "\t<tr><td>4.1</td><td>I was statisfied with the module.                      </td><td>Overall                </td><td>89</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A grouped\\_df: 19 × 4\n",
       "\\begin{tabular}{llll}\n",
       " QUE\\_CODE & QUE\\_TEXT & CAT\\_NAME & pct\\\\\n",
       " <chr> & <chr> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t 1.1 & Staff are good at explaining things.                    & Learning and Teaching   & 89\\\\\n",
       "\t 1.2 & Staff made the subject interesting.                     & Learning and Teaching   & 82\\\\\n",
       "\t 1.3 & The module was intellectually stimulating.              & Learning and Teaching   & 82\\\\\n",
       "\t 1.4 & The aims and objectives were clearly stated.            & Learning and Teaching   & 89\\\\\n",
       "\t 1.5 & The module was well-organised and ran smoothly.         & Learning and Teaching   & 78\\\\\n",
       "\t 1.6 & The pace was appropriate.                               & Learning and Teaching   & 80\\\\\n",
       "\t 1.7 & The level was appropriate.                              & Learning and Teaching   & 82\\\\\n",
       "\t 1.8 & The workload was managable.                             & Learning and Teaching   & 78\\\\\n",
       "\t 1.9 & I was able to contact module staff when I needed to.    & Learning and Teaching   & 76\\\\\n",
       "\t 2.1 & The assessment requirements were clearly stated.        & Assessment and Feedback & 84\\\\\n",
       "\t 2.2 & The criteria for marking was made clear to me.          & Assessment and Feedback & 82\\\\\n",
       "\t 2.3 & I was well supported for the assessment.                & Assessment and Feedback & 80\\\\\n",
       "\t 2.4 & I was provided with feedback that aided understanding.  & Assessment and Feedback & 80\\\\\n",
       "\t 2.5 & Feedback was provided within three weeks of submission. & Assessment and Feedback & 80\\\\\n",
       "\t 3.1 & The module was well supported by moodle.                & Learning Resources      & 73\\\\\n",
       "\t 3.2 & The library resources met my needs.                     & Learning Resources      & 82\\\\\n",
       "\t 3.3 & The IT resources met my needs.                          & Learning Resources      & 89\\\\\n",
       "\t 3.4 & The rooms/facilities were of good quality.              & Learning Resources      & 78\\\\\n",
       "\t 4.1 & I was statisfied with the module.                       & Overall                 & 89\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A grouped_df: 19 × 4\n",
       "\n",
       "| QUE_CODE &lt;chr&gt; | QUE_TEXT &lt;chr&gt; | CAT_NAME &lt;chr&gt; | pct &lt;dbl&gt; |\n",
       "|---|---|---|---|\n",
       "| 1.1 | Staff are good at explaining things.                    | Learning and Teaching   | 89 |\n",
       "| 1.2 | Staff made the subject interesting.                     | Learning and Teaching   | 82 |\n",
       "| 1.3 | The module was intellectually stimulating.              | Learning and Teaching   | 82 |\n",
       "| 1.4 | The aims and objectives were clearly stated.            | Learning and Teaching   | 89 |\n",
       "| 1.5 | The module was well-organised and ran smoothly.         | Learning and Teaching   | 78 |\n",
       "| 1.6 | The pace was appropriate.                               | Learning and Teaching   | 80 |\n",
       "| 1.7 | The level was appropriate.                              | Learning and Teaching   | 82 |\n",
       "| 1.8 | The workload was managable.                             | Learning and Teaching   | 78 |\n",
       "| 1.9 | I was able to contact module staff when I needed to.    | Learning and Teaching   | 76 |\n",
       "| 2.1 | The assessment requirements were clearly stated.        | Assessment and Feedback | 84 |\n",
       "| 2.2 | The criteria for marking was made clear to me.          | Assessment and Feedback | 82 |\n",
       "| 2.3 | I was well supported for the assessment.                | Assessment and Feedback | 80 |\n",
       "| 2.4 | I was provided with feedback that aided understanding.  | Assessment and Feedback | 80 |\n",
       "| 2.5 | Feedback was provided within three weeks of submission. | Assessment and Feedback | 80 |\n",
       "| 3.1 | The module was well supported by moodle.                | Learning Resources      | 73 |\n",
       "| 3.2 | The library resources met my needs.                     | Learning Resources      | 82 |\n",
       "| 3.3 | The IT resources met my needs.                          | Learning Resources      | 89 |\n",
       "| 3.4 | The rooms/facilities were of good quality.              | Learning Resources      | 78 |\n",
       "| 4.1 | I was statisfied with the module.                       | Overall                 | 89 |\n",
       "\n"
      ],
      "text/plain": [
       "   QUE_CODE QUE_TEXT                                               \n",
       "1  1.1      Staff are good at explaining things.                   \n",
       "2  1.2      Staff made the subject interesting.                    \n",
       "3  1.3      The module was intellectually stimulating.             \n",
       "4  1.4      The aims and objectives were clearly stated.           \n",
       "5  1.5      The module was well-organised and ran smoothly.        \n",
       "6  1.6      The pace was appropriate.                              \n",
       "7  1.7      The level was appropriate.                             \n",
       "8  1.8      The workload was managable.                            \n",
       "9  1.9      I was able to contact module staff when I needed to.   \n",
       "10 2.1      The assessment requirements were clearly stated.       \n",
       "11 2.2      The criteria for marking was made clear to me.         \n",
       "12 2.3      I was well supported for the assessment.               \n",
       "13 2.4      I was provided with feedback that aided understanding. \n",
       "14 2.5      Feedback was provided within three weeks of submission.\n",
       "15 3.1      The module was well supported by moodle.               \n",
       "16 3.2      The library resources met my needs.                    \n",
       "17 3.3      The IT resources met my needs.                         \n",
       "18 3.4      The rooms/facilities were of good quality.             \n",
       "19 4.1      I was statisfied with the module.                      \n",
       "   CAT_NAME                pct\n",
       "1  Learning and Teaching   89 \n",
       "2  Learning and Teaching   82 \n",
       "3  Learning and Teaching   82 \n",
       "4  Learning and Teaching   89 \n",
       "5  Learning and Teaching   78 \n",
       "6  Learning and Teaching   80 \n",
       "7  Learning and Teaching   82 \n",
       "8  Learning and Teaching   78 \n",
       "9  Learning and Teaching   76 \n",
       "10 Assessment and Feedback 84 \n",
       "11 Assessment and Feedback 82 \n",
       "12 Assessment and Feedback 80 \n",
       "13 Assessment and Feedback 80 \n",
       "14 Assessment and Feedback 80 \n",
       "15 Learning Resources      73 \n",
       "16 Learning Resources      82 \n",
       "17 Learning Resources      89 \n",
       "18 Learning Resources      78 \n",
       "19 Overall                 89 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ins_res %>% \n",
    "    filter(MOD_CODE=='SET08108' &\n",
    "           str_starts(AYR_CODE, '2016/7') & \n",
    "           PSL_CODE=='TR1') %>%\n",
    "    inner_join(ins_que, by=c(QUE_CODE=\"QUE_CODE\")) %>%\n",
    "    inner_join(ins_cat, by=c(CAT_CODE=\"CAT_CODE\")) %>%\n",
    "    mutate(score=floor(RES_VALU/4)) %>%\n",
    "    group_by(QUE_CODE, QUE_TEXT, CAT_NAME) %>%\n",
    "    summarise(pct=round(100*sum(score)/n()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Show the frequency chart for module SET08108 for question 4.1\n",
    "\n",
    "**For each response 1-5 show the number of students who gave that response (Module SET08108, 2016/7, TR1)**\n",
    "\n",
    "(note that this is not real data, these responses were randomly generated)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 3 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>RES_VALU</th><th scope=col>n</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>2</td><td> 6</td></tr>\n",
       "\t<tr><td>4</td><td>10</td></tr>\n",
       "\t<tr><td>5</td><td>39</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 2\n",
       "\\begin{tabular}{ll}\n",
       " RES\\_VALU & n\\\\\n",
       " <int> & <int>\\\\\n",
       "\\hline\n",
       "\t 2 &  6\\\\\n",
       "\t 4 & 10\\\\\n",
       "\t 5 & 39\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 2\n",
       "\n",
       "| RES_VALU &lt;int&gt; | n &lt;int&gt; |\n",
       "|---|---|\n",
       "| 2 |  6 |\n",
       "| 4 | 10 |\n",
       "| 5 | 39 |\n",
       "\n"
      ],
      "text/plain": [
       "  RES_VALU n \n",
       "1 2         6\n",
       "2 4        10\n",
       "3 5        39"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ins_res %>%\n",
    "    filter(MOD_CODE=='SET08108' &\n",
    "           str_starts(AYR_CODE, '2016/7') & \n",
    "           PSL_CODE=='TR1' &\n",
    "           QUE_CODE=='4.1') %>%\n",
    "    group_by(RES_VALU) %>%\n",
    "    summarise(n=n())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
